﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LoadNorthwindDB;
using System.Data.Entity;
using System.Data.Objects.DataClasses;

namespace Ex8.InheritEmployeeEntityClass
{
    public class TerritoriesInfo
    {
        public string TerritoryID { get; set; }
        public string TerritoryDescription { get; set; }
    }

    public class EmployeeExtended : LoadNorthwindDB.Employee
    {
        public new EntityCollection<TerritoriesInfo> Territories
        {
            get
            {
                NorthwindEntities contex = new NorthwindEntities();
                string nativeSQLQuery =
                    "SELECT t.TerritoryID,t.TerritoryDescription FROM Employees e " +
                    "INNER JOIN EmployeeTerritories et " +
                    "ON e.EmployeeID = et.EmployeeID " +
                    "INNER JOIN Territories t " +
                    "ON et.TerritoryID = t.TerritoryID " +
                    "WHERE e.EmployeeID = {0}";
                object[] parameters = { base.EmployeeID };
                IEnumerable<TerritoriesInfo> employees = 
                    contex.ExecuteStoreQuery<TerritoriesInfo>(nativeSQLQuery, parameters);
                EntityCollection<TerritoriesInfo> result = new EntityCollection<TerritoriesInfo>();
                foreach (var item in employees)
                {
                    result.Add(item);
                }
                return result;
            }
        }
        
        public EmployeeExtended(int index) : base()
        {
            NorthwindEntities contex = new NorthwindEntities();
            Employee employee = contex.Employees.FirstOrDefault(e => e.EmployeeID == index);
            base.EmployeeID = employee.EmployeeID;
            base.Address = employee.Address;
            base.BirthDate = employee.BirthDate;
            base.City = employee.City;
            base.FirstName = employee.FirstName;
            base.LastName = employee.LastName;
            //...
        }
    }

    public class EmployeeTest
    {
        public static void Main()
        {
            EmployeeExtended myEmployee = new EmployeeExtended(3);
            EntityCollection<TerritoriesInfo> currentEmployeeTerritories = myEmployee.Territories;
            
            Console.WriteLine("Territories of {0}: \n",myEmployee.FirstName);

            Console.WriteLine("{0,-11}: {1}\n","TerritoryID ","Description");
            foreach (TerritoriesInfo currentTerritory in currentEmployeeTerritories)
            {
                Console.WriteLine("{0,-11} : {1}", currentTerritory.TerritoryID, currentTerritory.TerritoryDescription);
            }
        }
    }
}
